﻿-----------------------------------------------------------------------------------------------
-----------------------------------pca_courier.agentes-----------------------------------------
-----------------------------------------------------------------------------------------------

CREATE SEQUENCE pca_courier.seq_agente_id;
CREATE TABLE pca_courier.agentes
(
	agente_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(nextval('pca_courier.seq_agente_id')::integer),
	sesion_id bigint NOT NULL,
	pais_id varchar(10) NOT NULL,
	doc_identidad_id varchar(5) NOT NULL,
	num_doc_identidad varchar(20) NOT NULL,
	nombre varchar(150) NOT NULL,
	direccion varchar(150) NOT NULL,
	telefono varchar(50),
	email varchar(50),
	fecha_registro timestamp with time zone NOT NULL DEFAULT current_timestamp,
	activo boolean NOT NULL DEFAULT true,
	CONSTRAINT agentes_pk PRIMARY KEY(agente_id),
	CONSTRAINT sesiones_fkey FOREIGN KEY(sesion_id)
		REFERENCES pca_log.sesiones(sesion_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT paises_fkey FOREIGN KEY(pais_id)
		REFERENCES pca_tablas.paises(pais_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT doc_cliente_key UNIQUE(pais_id,doc_identidad_id,num_doc_identidad),--cada cliente debe tener un documento unico dependiendo del pais
	CONSTRAINT docs_indentidad_fkey FOREIGN KEY(doc_identidad_id)
		REFERENCES pca_tablas.docs_identidad(doc_identidad_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE OR REPLACE VIEW pca_courier.vw_agentes AS
SELECT
	e.agente_id,
	e.sesion_id,
	e.pais_id,
	p.nombre AS pais,
	e.doc_identidad_id,
	d.siglas as doc_identidad,
	e.num_doc_identidad,
	e.nombre,
	e.direccion,
	e.telefono,
	e.email,
	e.fecha_registro,
	e.activo
FROM
	 pca_courier.agentes e
INNER JOIN
	pca_tablas.paises p
ON
	(e.pais_id=p.pais_id)
INNER JOIN
	pca_tablas.docs_identidad d
ON
	(e.doc_identidad_id=d.doc_identidad_id);

DROP TRIGGER IF EXISTS tri_count_rows_agentes ON pca_courier.agentes;
CREATE TRIGGER tri_count_rows_agentes AFTER INSERT OR DELETE ON pca_courier.agentes
FOR EACH ROW
EXECUTE PROCEDURE pca_tablas.fn_count_rows('pca_courier.agentes');

--INSERT INTO pca_tablas.count_rows_tables VALUES('pca_courier.agentes',0);

CREATE OR REPLACE FUNCTION pca_courier.fn_add_agente
				(bigint,--sesion_id $1
				varchar,--pais_id $2
				varchar,--doc_identidad_id $3
				varchar,--num_doc_identidad $4
				varchar,--nombre $5
				varchar,--direccion $6
				varchar,--telefono $7
				varchar --email $8
				)RETURNS bigint AS
$$
BEGIN
	$5 = initcap(trim(both ' ' from $5));
	$6 = initcap(trim(both ' ' from $6));
	$8 = lower(trim(both ' ' from $8));
	
	INSERT INTO pca_courier.agentes
		(sesion_id,
		pais_id,
		doc_identidad_id,
		num_doc_identidad,
		nombre,
		direccion,
		telefono,
		email)
	VALUES
		($1,$2,$3,$4,$5,$6,$7,$8);

	RETURN  pca_global.fn_pseudo_encrypt_bigint(currval('pca_courier.seq_agente_id')::integer);
EXCEPTION
	WHEN unique_violation THEN
		RAISE EXCEPTION 'Numero de documento repetido';
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_update_agente
				(bigint,--agente_id $1
				bigint,--sesion_id $2
				varchar,--pais_id $3
				varchar,--doc_identidad_id $4
				varchar,--num_doc_identidad $5
				varchar,--nombre $6
				varchar,--direccion $7
				varchar,--telefono $8
				varchar,--email $9
				boolean --activo $10
				)RETURNS void AS
$$
BEGIN
	$6 = initcap(trim(both ' ' from $6));
	$7 = initcap(trim(both ' ' from $7));
	$9 = lower(trim(both ' ' from $9));

	UPDATE pca_courier.agentes SET
		sesion_id=$2,
		pais_id=$3,
		doc_identidad_id=$4,
		num_doc_identidad=$5,
		nombre=$6,
		direccion=$7,
		telefono=$8,
		email=$9,
		activo=$10
	WHERE
		agente_id=$1;
EXCEPTION
	WHEN unique_violation THEN
		RAISE EXCEPTION 'Numero de documento repetido';
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_del_agente
				(bigint --agente_id $1
				)RETURNS void AS
$$
BEGIN
	DELETE FROM 
		pca_courier.agentes
	WHERE
		agente_id = $1;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_agentes
				(integer,--start $1
				integer --limit $2
				)RETURNS SETOF refcursor AS
$$
DECLARE
	cursor_result refcursor;
	cursor_totalrows refcursor;
BEGIN
	OPEN cursor_result FOR
		SELECT
			agente_id,
			sesion_id,
			pais_id,
			pais,
			doc_identidad_id,
			doc_identidad,
			num_doc_identidad,
			nombre,
			direccion,
			telefono,
			email,
			fecha_registro,
			activo
		FROM
			 pca_courier.vw_agentes
		ORDER BY
			fecha_registro ASC LIMIT $2 OFFSET $1;
	RETURN NEXT cursor_result;

	OPEN cursor_totalrows FOR
		SELECT pca_tablas.fn_get_count_rows_table('pca_courier.agentes');
	RETURN NEXT cursor_totalrows;	
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_agentes
				(integer,--start $1
				integer, --limit $2
				varchar[] --filtros $3
				)RETURNS SETOF refcursor AS
$$
DECLARE
	cursor_result refcursor;
	cursor_totalrows refcursor;
	sqlfiltro varchar;
	sqlcount varchar;
BEGIN
	sqlfiltro := 
		'SELECT
			agente_id,
			sesion_id,
			pais_id,
			pais,
			doc_identidad_id,
			doc_identidad,
			num_doc_identidad,
			nombre,
			direccion,
			telefono,
			email,
			fecha_registro,
			activo
		FROM
			pca_courier.vw_agentes
		WHERE '|| 
			pca_global.fn_get_strfiltro($3)|| 
		' ORDER BY
			fecha_registro ASC LIMIT '|| $2 || ' OFFSET '|| $1 ;

	sqlcount := 
		'SELECT
			count(agente_id)
		FROM
			pca_courier.vw_agentes
		WHERE '|| 
			pca_global.fn_get_strfiltro($3);
	
	OPEN cursor_result FOR
		EXECUTE(sqlfiltro);
	RETURN NEXT cursor_result;

	OPEN cursor_totalrows FOR
		EXECUTE(sqlcount);
	RETURN NEXT cursor_totalrows;	
END;
$$
LANGUAGE plpgsql;

-----------------------------------------------------------------------------------------------
--------------------------------pca_courier.agentes_destinos-----------------------------------
-----------------------------------------------------------------------------------------------

CREATE TABLE pca_courier.agentes_destinos
(
	agente_id bigint NOT NULL,
	destino_id varchar(2) NOT NULL,
	CONSTRAINT agentes_destinos_pk PRIMARY KEY(agente_id,destino_id),
	CONSTRAINT agentes_fkey FOREIGN KEY(agente_id)
		REFERENCES pca_courier.agentes(agente_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT destinos_fkey FOREIGN KEY(destino_id)
		REFERENCES pca_courier.destinos(destino_id) MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE OR REPLACE VIEW pca_courier.vw_agentes_destinos AS
SELECT
	ad.agente_id,
	ad.destino_id,
	a.nombre,
	a.activo
FROM
	pca_courier.agentes_destinos ad
INNER JOIN
	pca_courier.agentes a
ON
	(ad.agente_id=a.agente_id);

CREATE OR REPLACE FUNCTION pca_courier.fn_add_agente_destino(bigint,--agente_id $1
							     varchar --destino_id $2
							     )RETURNS void AS
$$
BEGIN
	INSERT INTO pca_courier.agentes_estaciones
		(agente_id,
		destino_id)
	VALUES
		($1,$2);
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_del_agente_destino(integer,--agente_id $1
							      integer--estacion_id $2
							      )RETURNS void AS
$$
BEGIN
	DELETE FROM 
		pca_courier.agentes_estaciones
	WHERE 
		agente_id=$1 AND
		estacion_id=$2;
END;
$$
LANGUAGE plpgsql;

--Devuelve todos los destinos a los cuales distribuye un agente
CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_agentes_estaciones(bigint --agente_id $1
							         )RETURNS SETOF pca_courier.agentes_destinos AS
$$
DECLARE
	_row pca_courier.agentes_destinos%ROWTYPE;
BEGIN
	FOR _row IN 
		SELECT
			agente_id,
			destino_id
		FROM
			pca_courier.agentes_destinos
		WHERE
			agente_id=$1
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

--Devuelve los agentes activos para un destino
CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_agentes_activos(varchar --destino_id $1
								   )RETURNS SETOF pca_courier.vw_agentes_destinos AS
$$
DECLARE
	_row pca_courier.vw_agentes_destinos%ROWTYPE;
BEGIN
	FOR _row IN
		SELECT
			agente_id,
			estacion_id,
			nombre,
			activo
		FROM
			pca_courier.vw_agentes_destinos
		WHERE
			estacion_id=$1 AND
			activo=true
		ORDER BY
			agente_id
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;